Release 10.1A: OpenEdge Data Management:
SQL Development

Handling cycles in referential integrity

A cycle exists when a series of primary-key-foreign-key relationships exists within a group of tables in a database.

In Example 5–23, the parts.distrib_no column references the primary key of the distributor table, and the distributor.part_no column references the primary key of the parts table. Each of the tables references the other, forming a cycle.

Example 5–23: Table reference cycle

CREATE TABLE parts ( part_no INTEGER NOT NULL PRIMARY KEY, part_name CHAR (19), distrib_no INTEGER REFERENCES distributor ) ; CREATE TABLE distributor ( distrib_no INTEGER NOT NULL PRIMARY KEY, distrib_name CHAR (19), address CHAR (30), phone_no CHAR (10), part_no INTEGER REFERENCES parts ) ;

A special case of the cycle in referential integrity occurs when a foreign key of a table references the primary key of the same table. Example 5–24 shows this single-table cycle.

Example 5–24: SIngle-table reference cycle

CREATE TABLE employee ( empno INTEGER NOT NULL PRIMARY KEY, ename CHAR (30) NOT NULL, deptno INTEGER NOT NULL, mgr_code INTEGER REFERENCES employee(empno) ) ;

Creating tables in cycles

Use the following procedure to create a table cycle.

To create a table cycle:

Create the first table with a reference to a table that is not yet created. Although the CREATE TABLE succeeds, it is marked incomplete. The INSERT, UPDATE, SELECT, and DELETE operations are not allowed on an incomplete table.
Create the referenced table with a primary or candidate key. The definition of the referencing table becomes complete. If the second table also contains a foreign key that references a table that is not yet created, this second table is also marked incomplete. This process continues until you create the last table.

Inserting rows in a cycle

Use the following procedure to insert rows in a cycle.

To insert rows into tables that form a cycle:

Insert rows into one of the tables that forms the cycle, with NULL values in the foreign key columns. If the foreign key is NULL, the database does not check for a match between the foreign key and the corresponding primary key. The insert succeeds. This is the referencing table.
Update or insert the values in the primary keys of the second, referenced table.
Update the foreign key values of the previous table, the referencing table.

Example 5–25 shows how to insert or update values into the employee table. This table forms a single-table cycle. First insert NULL into the mgr_code column. After you insert rows, update the values of the mgr_code column.

Example 5–25: Updating data in a single-table cycle

CREATE TABLE employee ( empno INTEGER NOT NULL PRIMARY KEY, ename CHAR (30) NOT NULL, deptno INTEGER NOT NULL, mgr_code INTEGER REFERENCES employee (empno) ) ; INSERT INTO employee VALUES (100, 'JOHN', 10, NULL) ; INSERT INTO employee VALUES (500, 'MARY', 30, NULL) ; INSERT INTO employee VALUES (101, 'ANITA', 10, NULL) ; INSERT INTO employee VALUES (501, 'ROBERT', 30, NULL) ; UPDATE employee set mgr_code = 101 where empno = 100 ; UPDATE employee set mgr_code = 501 where empno = 500 ; /* ** Anita is John's manager. ** John's employee row references Anita's employee row. ** Robert is Mary's manager. ** Mary's employee row references Robert's employee row. ** The mgr_code is still NULL in Anita's row and in Robert's row. ** To set the mgr_code in Anita's row and Robert's row: ** 1. Insert rows for Anita's manager and Robert's manager ** 2. Update Anita's row and Robert's row */